package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import util.db.DBConn;
import dao.StudentMagDao;
import domain.Student;

/**
 * 
 * May 9, 2009
 *
 * @author Fabian
 *
 */
public class StudentMagImpl implements StudentMagDao {

	/* (non-Javadoc)
	 * @see dao.StudentMagDao#modifyStudent(vo.Student)
	 */
	
	public boolean modifyStudent(Student s) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "update t_student set number=?, password=?, name=?, sex=?, class_id=?, grade_id=?, major_id=? where id = ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, s.getNumber());
			pstmt.setString(2, s.getPassword());
			pstmt.setString(3, s.getName());
			pstmt.setString(4, s.getSex());
			pstmt.setInt(5, s.getClass_id());
			pstmt.setInt(6, s.getGrade_id());
			pstmt.setInt(7, s.getMajor_id());
			pstmt.setInt(8, s.getId());
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				result = true;
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.StudentMagDao#addStudent(vo.Student)
	 */
	
	public boolean addStudent(Student s) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "insert into t_student values (null, ?, ?, ?, ?, ?, ?, ?)";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, s.getNumber());
			pstmt.setString(2, s.getPassword());
			pstmt.setString(3, s.getName());
			pstmt.setString(4, s.getSex());
			pstmt.setInt(5, s.getClass_id());
			pstmt.setInt(6, s.getGrade_id());
			pstmt.setInt(7, s.getMajor_id());
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				result = true;
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}

	/* (non-Javadoc)
	 * @see dao.StudentMagDao#getPageStudent(int, int)
	 */
	
	public List<Student> getPageStudent(int start, int limit) {
		
		List<Student> list = new ArrayList<Student>();
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select stu.*, m.name as major, g.grade, c.class from t_student stu, t_major m , t_grade g, t_class c where stu.major_id = m.id and stu.grade_id = g.id and stu.class_id = c.id order by stu.id limit ?, ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, start);
			pstmt.setInt(2, limit);
			rs = pstmt.executeQuery();			
			while (rs.next()) {
				Student s = new Student();
				s.setId(rs.getInt("id"));
				s.setNumber(rs.getInt("number"));
				s.setPassword(rs.getString("password"));
				s.setName(rs.getString("name"));
				s.setSex(rs.getString("sex"));
				s.setClass_id(rs.getInt("class_id"));
				s.setGrade_id(rs.getInt("grade_id"));
				s.setMajor_id(rs.getInt("major_id"));
				s.setMajor(rs.getString("major"));
				s.setGrade(rs.getInt("grade"));
				list.add(s);
			}
			return list;
		} catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
	}

	/* (non-Javadoc)
	 * @see dao.StudentMagDao#getStudent(int)
	 */
	
	public Student getStudent(int id) {
		Student s = null;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select s.*, g.grade, m.name as m_name from t_student s, t_grade g, t_major m where s.grade_id = g.id and m.id = s.major_id and s.id = ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();			
			if (rs.next()) {
				s = new Student();
				s.setId(rs.getInt("id"));
				s.setNumber(rs.getInt("number"));
				s.setPassword(rs.getString("password"));
				s.setName(rs.getString("name"));
				s.setSex(rs.getString("sex"));
				s.setClass_id(rs.getInt("class_id"));
				s.setGrade_id(rs.getInt("grade_id"));
				s.setGrade(rs.getInt("grade"));
				s.setMajor_id(rs.getInt("major_id"));
				s.setMajor(rs.getString("m_name"));
				return s;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return s;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return s;
	}

	/* (non-Javadoc)
	 * @see dao.StudentMagDao#gettotalCount()
	 */
	
	public int getTotalCount() {
		int totalCount = 0;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select count(*) from t_student";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			rs = pstmt.executeQuery();			
			if (rs.next()) {
				totalCount = rs.getInt(1);
				return totalCount;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return totalCount;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return totalCount;
	}

	/* (non-Javadoc)
	 * @see dao.StudentMagDao#getStudent(java.lang.String, java.lang.String)
	 */
	
	public Student getStudent(int num, String pwd) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from t_student where number = ? and password = ?";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			pstmt.setInt(1, num);
			pstmt.setString(2, pwd);
			rs = pstmt.executeQuery();			
			if (rs.next()) {
				Student s = new Student();
				s.setId(rs.getInt("id"));
				s.setNumber(rs.getInt("number"));
				s.setPassword(rs.getString("password"));
				s.setName(rs.getString("name"));
				s.setSex(rs.getString("sex"));
				s.setClass_id(rs.getInt("class_id"));
				s.setGrade_id(rs.getInt("grade_id"));
				s.setMajor_id(rs.getInt("major_id"));
				return s;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return null;
	}

	/* (non-Javadoc)
	 * @see dao.StudentMagDao#delStudents(java.lang.String)
	 */
	
	public boolean delStudents(String thIdsStr) {
		boolean result = false;
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "delete from t_student where id in ( " + thIdsStr + " )";
		try {
			conn = DBConn.getConn();
			pstmt = DBConn.getPstmt(conn, sql);
			int rsColum = pstmt.executeUpdate();		
			if (rsColum > 0) {
				result = true;
				return result;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBConn.close(conn);
			DBConn.close(pstmt);
			DBConn.close(rs);
		}
		
		return result;
	}
	
}
